﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using QuanLyDuAn.DTO;
using QuanLyDuAn.DataAccess;
using System.Data;
using System.Collections;

namespace QuanLyDuAn.BUS
{
    class InterviewPlanController
    {
        DataSet ds,dsSearch;
        ArrayList list = new ArrayList();
        SqlCommand cmd;
        SqlDataAdapter da;
        SqlConnection conn;
        SqlDataReader reader;
        dbAccess database = new dbAccess();
        public DataSet LoadInterviewPlan()
        {
            conn = database.GetConnect();
            conn.Open();
            ds = new DataSet();
            cmd = new SqlCommand("getInterview_Plan", conn);
            da = new SqlDataAdapter(cmd);
            da.Fill(ds, "Interview_Plan");
            return ds;
        }
        public void InsertInterviewPlan(String interviewer, String applicant, String date,String time, String status)
        {
            conn = database.GetConnect();
            conn.Open();
            cmd = new SqlCommand("insertInterviewPlan", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@iId", SqlDbType.VarChar).Value = interviewer;
            cmd.Parameters.Add("@aId", SqlDbType.VarChar).Value = applicant;
            cmd.Parameters.Add("@IpDate", SqlDbType.DateTime).Value = date;
            cmd.Parameters.Add("@IpTime", SqlDbType.VarChar).Value = time;
            cmd.Parameters.Add("@IpStatus", SqlDbType.VarChar).Value = status;
            int i = cmd.ExecuteNonQuery();
        }
        public ArrayList LoadPlans()
        {
            CheckPlans plans;
            conn = database.GetConnect();
            conn.Open();
            cmd = new SqlCommand("getInterview_Plan", conn);
            da = new SqlDataAdapter(cmd);
            reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                String interview = reader.GetString(0);
                String applicant = reader.GetString(1);
                plans = new CheckPlans(interview, applicant);
                list.Add(plans);
            }
            return list;
        }

        public void UpdateInterviewPlan(String interviewer, String applicant, String date,String time, String status)
        {
            conn = database.GetConnect();
            conn.Open();
            cmd = new SqlCommand("updateInterviewPlan", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@iId", SqlDbType.VarChar).Value = interviewer;
            cmd.Parameters.Add("@aId", SqlDbType.VarChar).Value = applicant;
            cmd.Parameters.Add("@IpDate", SqlDbType.DateTime).Value = date;
            cmd.Parameters.Add("@IpTime", SqlDbType.VarChar).Value = time;
            cmd.Parameters.Add("@IpStatus", SqlDbType.VarChar).Value = status;
            int i = cmd.ExecuteNonQuery();
        }

        public void DelInterviewPlan(String applicant)
        {
            conn = database.GetConnect();
            conn.Open();
            cmd = new SqlCommand("DelInterviewPlan", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@aId", SqlDbType.VarChar).Value = applicant;
            int i = cmd.ExecuteNonQuery();
        }
        public DataSet SearchPlanByInterviewer(String interviewer)
        {
            conn = database.GetConnect();
            conn.Open();
            dsSearch = new DataSet();
            cmd = new SqlCommand("searchPlanInterviewer", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@iId", SqlDbType.VarChar).Value = interviewer;
            da = new SqlDataAdapter(cmd);
            da.Fill(dsSearch, "Interview_Plan");
            return dsSearch;
        }
        public DataSet SearchPlanByDate(DateTime date)
        {
            conn = database.GetConnect();
            conn.Open();
            dsSearch = new DataSet();
            cmd = new SqlCommand("searchInterviewPlanByDate", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@IpDate", SqlDbType.VarChar).Value = date;
            da = new SqlDataAdapter(cmd);
            da.Fill(dsSearch, "Interview_Plan");
            return dsSearch;
        }
        public DataSet SearchPlanByStatus(String status)
        {
            conn = database.GetConnect();
            conn.Open();
            dsSearch = new DataSet();
            cmd = new SqlCommand("searchInterviewPlanByStatus", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@IpStatus", SqlDbType.VarChar).Value = status;
            da = new SqlDataAdapter(cmd);
            da.Fill(dsSearch, "Interview_Plan");
            return dsSearch;
        }
        public DataSet StatisticsInterviewPlan(String datetime1, String datetime2)
        {
            conn = database.GetConnect();
            conn.Open();
            ds = new DataSet();
            cmd = new SqlCommand("statisticsInterviewPlan", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@aDate1", SqlDbType.DateTime).Value = datetime1;
            cmd.Parameters.Add("@aDate2", SqlDbType.DateTime).Value = datetime2;
            da = new SqlDataAdapter(cmd);
            da.Fill(ds, "Interview_Plan");
            return ds;
        }
    }
}
